﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using System.Data;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// PaperTopic数据类
    /// </summary>
    public class PaperTopic
    {

        /// <summary>
        /// 是否存在记录
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="fPaperId">编号（主键）</param>
        /// <returns>是否存在</returns>
        public bool ExistsPaperID(decimal fPaperId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_PaperTopic");
            strSql.Append(" where FPaperId=@FPaperId");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperId", SqlDbType.Decimal)
			};
            parameters[0].Value = fPaperId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 题目修改保存 事务模式
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="topicModel">题目对象</param>
        /// <param name="answerOptionList">答案集合</param>
        public void UpdTopicAndAnswerOption(Model.PaperTopic topicModel, List<Model.PaperAnswerOption> answerOptionList)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //修改题目
            strSql.Append("update t_PaperTopic set ");
            strSql.Append("FPaperId=@FPaperId,");
            strSql.Append("FTopic=@FTopic,");
            strSql.Append("FTopicType=@FTopicType,");
            strSql.Append("FStdAnswer=@FStdAnswer,");
            strSql.Append("FShortAnswerCount=@FShortAnswerCount,");
            strSql.Append("FPubDate=@FPubDate,");
            strSql.Append("FPubDeptNo=@FPubDeptNo,");
            strSql.Append("FPubEmpNo=@FPubEmpNo,");
            strSql.Append("FPubIp=@FPubIp");
            strSql.Append(" where FPaperTopicId=@FPaperTopicId");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperId", SqlDbType.Decimal),
					new SqlParameter("@FTopic", SqlDbType.NVarChar),
					new SqlParameter("@FTopicType", SqlDbType.Char,1),
					new SqlParameter("@FStdAnswer", SqlDbType.VarChar,6),
					new SqlParameter("@FShortAnswerCount", SqlDbType.Int),
					new SqlParameter("@FPubDate", SqlDbType.DateTime),
					new SqlParameter("@FPubDeptNo", SqlDbType.VarChar,30),
					new SqlParameter("@FPubEmpNo", SqlDbType.VarChar,30),
					new SqlParameter("@FPubIp", SqlDbType.VarChar,15),
					new SqlParameter("@FPaperTopicId", SqlDbType.Decimal)};
            parameters[0].Value = topicModel.FPaperId;
            parameters[1].Value = topicModel.FTopic;
            parameters[2].Value = topicModel.FTopicType;
            parameters[3].Value = topicModel.FStdAnswer;
            parameters[4].Value = topicModel.FShortAnswerCount;
            parameters[5].Value = topicModel.FPubDate;
            parameters[6].Value = topicModel.FPubDeptNo;
            parameters[7].Value = topicModel.FPubEmpNo;
            parameters[8].Value = topicModel.FPubIp;
            parameters[9].Value = topicModel.FPaperTopicId;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            string strSqls = string.Empty;
            //修改数据库中已存在数据答案编号变更
            //if (updAnswerOptionList.Count>0)
            //{
            //    strSql = new StringBuilder();
            //    strSql.Append("update t_PaperAnswerOption set ");
            //    strSql.Append("FAnswerNo=@FAnswerNoNew");
            //    strSql.Append(" where FPaperTopicId=@FPaperTopicId and FAnswerNo=@FAnswerNo ");
            //    strSqls = strSql.ToString();

            //    foreach (Model.PaperAnswerOption model in updAnswerOptionList) {
            //        cmds = new CommandInfo();

            //        SqlParameter[] parameters1 = {
            //            new SqlParameter("@FAnswerNoNew", SqlDbType.Char,1),
            //            new SqlParameter("@FPaperTopicId", SqlDbType.Decimal),
            //            new SqlParameter("@FAnswerNo", SqlDbType.Char,1)};
            //        parameters1[0].Value = model.FAnswerContent;
            //        parameters1[1].Value = model.FPaperTopicId;
            //        parameters1[2].Value = model.FAnswerNo;

            //        cmds.CommandText = strSqls;
            //        cmds.Parameters = parameters1;
            //        cmdList.Add(cmds);
            //    }
            //}

            //增加题目选择项
            if (answerOptionList.Count > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into t_PaperAnswerOption(");
                strSql.Append("FPaperTopicId,FAnswerNo,FAnswerContent,FIsRestsAnswer,FRestsAnswerCount)");
                strSql.Append(" values (");
                strSql.Append("@FPaperTopicId,@FAnswerNo,@FAnswerContent,@FIsRestsAnswer,@FRestsAnswerCount)");
                strSqls = strSql.ToString();

                foreach (Model.PaperAnswerOption model in answerOptionList)
                {
                    cmds = new CommandInfo();

                    SqlParameter[] parameters1 = {
					    new SqlParameter("@FPaperTopicId", SqlDbType.Decimal),
					    new SqlParameter("@FAnswerNo", SqlDbType.Char,1),
					    new SqlParameter("@FAnswerContent", SqlDbType.NVarChar),
					    new SqlParameter("@FIsRestsAnswer", SqlDbType.Char,1),
					    new SqlParameter("@FRestsAnswerCount", SqlDbType.Int)};
                    parameters1[0].Value = model.FPaperTopicId;
                    parameters1[1].Value = model.FAnswerNo;
                    parameters1[2].Value = model.FAnswerContent;
                    parameters1[3].Value = model.FIsRestsAnswer;
                    parameters1[4].Value = model.FRestsAnswerCount;

                    cmds.CommandText = strSqls;
                    cmds.Parameters = parameters1;
                    cmdList.Add(cmds);

                }

            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);


        }

        /// <summary>
        /// 删除题目和题目选择项 事务模式
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="topicID">编号</param>
        public void DelTopicAndAnswerOption(decimal topicID)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //删除题目删除题目选择项
            strSql.Append("delete from t_PaperAnswerOption ");
            strSql.Append(" where FPaperTopicId=@FPaperTopicId");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperTopicId", SqlDbType.Decimal)};
            parameters[0].Value = topicID;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //删除题目
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("delete from t_PaperTopic ");
            strSql.Append(" where FPaperTopicId=@FPaperTopicId");


            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);

        }

        /// <summary>
        /// 批量删除题目和题目选择项 事务模式
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="topicIdList">编号集合</param>
        public void DelTopicAndAnswerOptionMany(List<decimal> topicIdList)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //删除题目选择项
            strSql.Append("delete from t_PaperAnswerOption ");
            strSql.Append(" where FPaperTopicId=@FPaperTopicId");
            string answerOptionSql = strSql.ToString();
            //删除题目
            strSql = new StringBuilder();
            strSql.Append("delete from t_PaperTopic ");
            strSql.Append(" where FPaperTopicId=@FPaperTopicId");
            string topicSql = strSql.ToString();

            foreach (decimal topicId in topicIdList)
            {
                cmds = new CommandInfo();

                SqlParameter[] parameters = {
					new SqlParameter("@FPaperTopicId", SqlDbType.Decimal)};
                parameters[0].Value = topicId;

                cmds.CommandText = answerOptionSql;
                cmds.Parameters = parameters;
                cmdList.Add(cmds);

                cmds = new CommandInfo();

                cmds.CommandText = topicSql;
                cmds.Parameters = parameters;
                cmdList.Add(cmds);

            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);


        }


        /// <summary>
        /// 得到一个实体对象
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="fPaperTopicId">题目主题编号</param>
        /// <returns>Model对象</returns>
        public Model.PaperTopic GetModel(decimal fPaperTopicId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FPaperId,FTopic,FTopicType,FPaperTopicId,FStdAnswer,FShortAnswerCount,FPubDate,FPubDeptNo,FPubEmpNo,FPubIp from t_PaperTopic ");
            strSql.Append(" where FPaperTopicId=@FPaperTopicId");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperTopicId", SqlDbType.Decimal)
			};
            parameters[0].Value = fPaperTopicId;

            Model.PaperTopic model = new Model.PaperTopic();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["FPaperId"] != null && ds.Tables[0].Rows[0]["FPaperId"].ToString() != "")
                {
                    model.FPaperId = decimal.Parse(ds.Tables[0].Rows[0]["FPaperId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FTopic"] != null && ds.Tables[0].Rows[0]["FTopic"].ToString() != "")
                {
                    model.FTopic = ds.Tables[0].Rows[0]["FTopic"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FTopicType"] != null && ds.Tables[0].Rows[0]["FTopicType"].ToString() != "")
                {
                    model.FTopicType = ds.Tables[0].Rows[0]["FTopicType"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FPaperTopicId"] != null && ds.Tables[0].Rows[0]["FPaperTopicId"].ToString() != "")
                {
                    model.FPaperTopicId = decimal.Parse(ds.Tables[0].Rows[0]["FPaperTopicId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FStdAnswer"] != null && ds.Tables[0].Rows[0]["FStdAnswer"].ToString() != "")
                {
                    model.FStdAnswer = ds.Tables[0].Rows[0]["FStdAnswer"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FShortAnswerCount"] != null && ds.Tables[0].Rows[0]["FShortAnswerCount"].ToString() != "")
                {
                    model.FShortAnswerCount = int.Parse(ds.Tables[0].Rows[0]["FShortAnswerCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FPubDate"] != null && ds.Tables[0].Rows[0]["FPubDate"].ToString() != "")
                {
                    model.FPubDate = DateTime.Parse(ds.Tables[0].Rows[0]["FPubDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FPubDeptNo"] != null && ds.Tables[0].Rows[0]["FPubDeptNo"].ToString() != "")
                {
                    model.FPubDeptNo = ds.Tables[0].Rows[0]["FPubDeptNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FPubEmpNo"] != null && ds.Tables[0].Rows[0]["FPubEmpNo"].ToString() != "")
                {
                    model.FPubEmpNo = ds.Tables[0].Rows[0]["FPubEmpNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FPubIp"] != null && ds.Tables[0].Rows[0]["FPubIp"].ToString() != "")
                {
                    model.FPubIp = ds.Tables[0].Rows[0]["FPubIp"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 保存题目和题目选择项 事务模式
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-25</date>
        /// <param name="topicModel">题目对象</param>
        /// <param name="answerOptionList">答案集合</param>
        public void AddTopicAndAnswerOption(Model.PaperTopic topicModel, List<Model.PaperAnswerOption> answerOptionList)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //增加题目
            strSql.Append("insert into t_PaperTopic(");
            strSql.Append("FPaperId,FTopic,FTopicType,FStdAnswer,FShortAnswerCount,FPubDate,FPubDeptNo,FPubEmpNo,FPubIp)");
            strSql.Append(" values (");
            strSql.Append("@FPaperId,@FTopic,@FTopicType,@FStdAnswer,@FShortAnswerCount,@FPubDate,@FPubDeptNo,@FPubEmpNo,@FPubIp)");
            strSql.Append(";select @returnID=IDENT_CURRENT('t_PaperTopic')");
            SqlParameter[] parameters = {
					new SqlParameter("@FPaperId", SqlDbType.Decimal),
					new SqlParameter("@FTopic", SqlDbType.NVarChar),
					new SqlParameter("@FTopicType", SqlDbType.Char,1),
					new SqlParameter("@FStdAnswer", SqlDbType.VarChar,6),
					new SqlParameter("@FShortAnswerCount", SqlDbType.Int),
					new SqlParameter("@FPubDate", SqlDbType.DateTime),
					new SqlParameter("@FPubDeptNo", SqlDbType.VarChar,30),
					new SqlParameter("@FPubEmpNo", SqlDbType.VarChar,30),
					new SqlParameter("@FPubIp", SqlDbType.VarChar,15),
                    new SqlParameter("@returnID", SqlDbType.Decimal)};
            parameters[0].Value = topicModel.FPaperId;
            parameters[1].Value = topicModel.FTopic;
            parameters[2].Value = topicModel.FTopicType;
            parameters[3].Value = topicModel.FStdAnswer;
            parameters[4].Value = topicModel.FShortAnswerCount;
            parameters[5].Value = topicModel.FPubDate;
            parameters[6].Value = topicModel.FPubDeptNo;
            parameters[7].Value = topicModel.FPubEmpNo;
            parameters[8].Value = topicModel.FPubIp;
            parameters[9].Direction = ParameterDirection.Output;


            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);


            //增加题目选择项
            if (answerOptionList.Count > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into t_PaperAnswerOption(");
                strSql.Append("FPaperTopicId,FAnswerNo,FAnswerContent,FIsRestsAnswer,FRestsAnswerCount)");
                strSql.Append(" values (");
                strSql.Append("@FPaperTopicId,@FAnswerNo,@FAnswerContent,@FIsRestsAnswer,@FRestsAnswerCount)");
                string strSqls = strSql.ToString();

                foreach (Model.PaperAnswerOption model in answerOptionList)
                {
                    cmds = new CommandInfo();

                    SqlParameter[] parameters1 = {
					    new SqlParameter("@FPaperTopicId", SqlDbType.Decimal),
					    new SqlParameter("@FAnswerNo", SqlDbType.Char,1),
					    new SqlParameter("@FAnswerContent", SqlDbType.NVarChar),
					    new SqlParameter("@FIsRestsAnswer", SqlDbType.Char,1),
					    new SqlParameter("@FRestsAnswerCount", SqlDbType.Int)};
                    parameters1[0].Direction = ParameterDirection.InputOutput;
                    parameters1[1].Value = model.FAnswerNo;
                    parameters1[2].Value = model.FAnswerContent;
                    parameters1[3].Value = model.FIsRestsAnswer;
                    parameters1[4].Value = model.FRestsAnswerCount;

                    cmds.CommandText = strSqls;
                    cmds.Parameters = parameters1;
                    cmdList.Add(cmds);

                }

            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);

        }



    }
}
